package cz.matasek.dbLogger.sql;

import cz.matasek.dbLogger.Constant;
import cz.matasek.dbLogger.Settings;
import cz.matasek.dbLogger.database.DatabaseConnection;
import cz.matasek.dbLogger.exception.DatabaseErrorException;
import cz.matasek.dbLogger.exception.DbLoggerRuntimeException;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.Charset;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;

/**
 * An abstract representation of SQL statement.
 * Every statement is uniquely identified by its statement type and database
 * platform.
 * <p>Optionally, this class brings the possibility of keyword replacements.</p>
 * <p>All statements are cached, so that statements used more than once are
 * read from cache, not from the physical device.</p>
 * <p>Performed statements are logged by this class.</p>
 *
 * @author Luboš Matásek
 */
public class SQLStatement {

    /**
     * SQL statement.
     */
    private String sql;
    /**
     * SQL statement with parameters bindings.
     */
    private String bindedSql;
    /**
     * Parameter bindings.
     */
    private String[] bindings;
    /**
     * Target database.
     */
    private DatabaseConnection database;
    /**
     * Unique type of statement.
     */
    private SQLStatementType statementType;
    /**
     * Prepared SQL statement object.
     */
    private PreparedStatement preparedStatement;
    /**
     * SQL statement object.
     */
    private Statement statement;
    /**
     * Size of the file reading buffer.
     */
    private static int FILE_BUFFER_SIZE = 4096;
    /**
     * Cache of SQL statements.
     * Statements are searched primarily in the cache. Only if not found are
     * read from filesystem.
     */
    public static Map<SQLStatementType, String> statementsCache;
    /**
     * List, containing already performed statements.
     */
    private static List<String> performedStatements;


    static {
        statementsCache = new HashMap<SQLStatementType, String>();
        performedStatements = new ArrayList<String>();
    }

    /**
     * Creates a new <code>SQLStatement</code> of the specified name for target
     * database.
     * Throws {@link cz.matasek.dbLogger.exception.DbLoggerRuntimeException}
     * when occurs error during reading SQL script.
     *
     * @param database target database
     * @param statementType unique statement type
     */
    public SQLStatement(DatabaseConnection database, SQLStatementType statementType) {
        this.database = database;
        this.statementType = statementType;

        try {
            // reads SQL statement
            readStatement();
        } catch (Exception ex) {
            throw new DbLoggerRuntimeException(ex.getMessage());
        }
    }

    /**
     * Logs currently performed statement.
     */
    private void log() {
        performedStatements.add(getSQL());
    }

    /**
     * Returns SQL code of the last performed statement.
     *
     * @return SQL code of the last performed statement
     */
    public static String getLastSQL() {
        return performedStatements.get(performedStatements.size() - 1);
    }

    /**
     * Reads the specified file.
     *
     * @param filename name of the file
     * @param charset charset of the file
     * @return content of the file
     * @throws FileNotFoundException if file does not exist
     * @throws IOException if an I/O error occurs
     */
    private final String readFile(String filename, Charset charset)
            throws FileNotFoundException, IOException {
        // opens stream for reading file
        FileInputStream fis;
        fis = new FileInputStream(filename);

        // opens stream fom reading with a specified charset
        InputStreamReader isr = new InputStreamReader(fis, charset);
        // opens stream for buffered reading
        BufferedReader br = new BufferedReader(isr);

        // reading buffer
        char[] buffer = new char[SQLStatement.FILE_BUFFER_SIZE];
        // content of the file
        StringBuilder fileContent = new StringBuilder();

        // reads the file
        while (br.read(buffer) != -1) {
            fileContent.append(buffer);
        }

        // closes all streams
        br.close();
        isr.close();
        fis.close();

        // trims the whole statement.
        return fileContent.toString().trim();
    }

    /**
     * Reads SQL statement with the specified name and target database.
     * If the statement is already in cache, it is copied from there. Otherwise
     * it is read from file.
     * 
     * @throws FileNotFoundException if file does not exist
     * @throws IOException if an I/O error occurs
     */
    private final void readStatement() throws FileNotFoundException, IOException {
        if (statementsCache.containsKey(statementType)) {
            bindedSql = statementsCache.get(statementType);
            sql = bindedSql.replaceAll("\r\n", "  ");
        } else {
            // name of the SQL script file
            String filename = Settings.get(
                    Settings.Key.SQL_DIRECTORY) + File.separator +
                    database.getPlatformName() + File.separator +
                    statementType + Constant.SQL_FILE_EXTENSION;

            // reads script from a file
            // replaces all newlines (because Oracle PL/SQL blocks must be in
            // a single row
            bindedSql = readFile(filename, Constant.SQL_FILE_CHARSET);
            sql = bindedSql.replaceAll("\r\n", "  ");

            // saves statement into cache
            statementsCache.put(statementType, bindedSql);
        }
    }

    /**
     * Replaces all occurences of the keyword with the specified replacement
     * string.
     *
     * @param keyword keyword to be substituted
     * @param replacement text to be substituted for the keyword
     */
    public final void replaceKeyword(ReplacementKeyword keyword, String replacement) {
        sql = sql.replaceAll(keyword.toString(), replacement);
        bindedSql = bindedSql.replaceAll(keyword.toString(), replacement);
    }

    /**
     * Returns this SQL statement as string.
     *
     * @return SQL statement
     */
    public final String getSQL() {
        if (bindings == null) {
            return bindedSql;
        } else {
            String returnSql = bindedSql;

            for (String parameter : bindings) {
                String quotedParameter = Matcher.quoteReplacement(parameter);
                returnSql = returnSql.replaceFirst("\\?", quotedParameter);
            }

            return returnSql;
        }
    }

    /**
     * Returns this SQL statement as raw string.
     *
     * @return SQL statement
     */
    public final String getRawSQL() {
        return sql;
    }

    /**
     * Executes the SQL statement, which may be an INSERT, UPDATE, or DELETE
     * statement or an SQL statement that returns nothing, such as an SQL DDL
     * statement.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @return either (1) the row count for SQL Data Manipulation Language (DML)
     * statements or (2) 0 for SQL statements that return nothing
     */
    public final int executeUpdate() {
        log();

        try {
            // creates statement
            Statement stmt = database.getConnection().createStatement();

            // performs query
            int result = stmt.executeUpdate(sql);

            // closes statement
            stmt.close();

            // sends result
            return result;
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Prepares a <code>PreparedStatement</code> object for sending
     * parameterized SQL statements to the database.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     */
    public final void prepareStatement() {
        try {
            preparedStatement = database.getConnection().prepareStatement(sql);

            bindings = new String[preparedStatement.getParameterMetaData().getParameterCount()];
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Sets the designated parameter to the PreparedStatement object.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @param parameterIndex the first parameter is 1, the second is 2, ...
     * @param parameterValue the parameter value
     */
    public final void setString(int parameterIndex, String parameterValue) {
        try {
            preparedStatement.setString(parameterIndex, parameterValue);

            bindings[parameterIndex - 1] = "'" + parameterValue + "'";
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Executes this SQL statement, which returns a single ResultSet object.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     *
     * @return a ResultSet object that contains the data produced by the given
     * query; never null
     */
    public final ResultSet executeQuery() {
        log();

        try {
            // performs either PreparedStatement or Statement
            if (preparedStatement != null) {
                return preparedStatement.executeQuery();
            } else {
                // creates sql
                statement = database.getConnection().createStatement();

                // performs query
                return statement.executeQuery(sql);
            }
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Closes currently opened statement.
     *
     * Can throw {@link cz.matasek.dbLogger.exception.DatabaseErrorException}
     * if database error occurs.
     */
    public final void close() {
        try {
            if (statement != null) {
                statement.close();
            }

            if (preparedStatement != null) {
                preparedStatement.close();
            }
        } catch (SQLException ex) {
            throw new DatabaseErrorException(ex);
        }
    }

    /**
     * Clears statements cache.
     */
    public static void clearStatementsCache() {
        statementsCache.clear();
    }
}
